How to Build a Rich Excel Report with ZK Spreadsheet
Ivan Cheng, Engineer, Potix Corporation
April 8, 2008
Applicable to ZK 3.0.4 and later
- Applicable to ZK Spreadsheet 1.0.0 beta and later
In this article, we will demonstrate how to build a rich Excel report with ZK Spreadsheet. If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:
Introduction
With ZK Spreadsheet, data from excel can be integrated into ZK framework and vice versa. For example, you can alter formats easily when using Excel sheets, such as border, font, color, size and set variable values. ZK Spreadsheet will display those format settings and variables from server side will show in the right cells. If you would like to update variable values, you only need to update the data source (ex:database). Although the layout (ex: cell position) and content are modified frequently, variable values can be calculated correctly.
Besides, you can also use ZK components to change cell contents as often as you like. Furthermore, you can access any cell of ZK Spreadsheet from the scope of ZK components.
Let's start to build an application for calculating balance sheet.
Live Demo
How to Build a Template Using Excel
Define the Layout Template
You can design the format of Excel data:
Define Name Ranges
Naming ranges is a method which Excel provides to assign a name in a range. You can also use naming ranges in formulas. If you want to know more about naming ranges, please check this URL: Naming Ranges
The following example defines several name ranges in BalanceSheet.xls, such as Quarter, LiquidAssets, FundInvestment etc.
Assign Cells to Name Ranges
After naming the ranges, we assign values of cells to those name ranges. With naming ranges, we don't need to know the actual cell positions anymore, but only the name.
Integrate Data with Name Ranges of Template
<window>
<listbox id="quarter" mold="select" rows="1" onSelect="quarter()">
<listitem value="0" label="Select"/>
<listitem value="1" label="Quarter 1"/>
<listitem value="2" label="Quarter 2"/>
<listitem value="3" label="Quarter 3"/>
<listitem value="4" label="Quarter 4"/>
</listbox>
//Import BalanceSheet.xls
<spreadsheet id="balance" url="/BalanceSheet.xls" maxrow="20" maxcolumn="20" height="600px" width="800px" />
<zscript>
import value.Database;
import value.DataBean;
Database database = new Database();
Book book = balance.getBook();
void quarter() {
Listitem listitem = quarter.getSelectedItem();
int quarter = Integer.parseInt(listitem.value);
//Call the method to access data from data source
DataBean dataBean = database.quaryData(quarter);
setCellValue("Quarter", dataBean.getItem());
setCellValue("LiquidAssets", dataBean.getLiquidAssets());
setCellValue("FundInvestment", dataBean.getFundInvestment());
......
setCellValue("TreasuryStock", dataBean.getTreasuryStock());
}
//This method gets the cell position of defined name range and set value into cells
void setCellValue(String rangeName,Object value){
Range range = book.lookupNameRange(rangeName);
Sheet sheet = range.getSheet();
//Use setCellValue(Top, Left, value) to set the value of cell
sheet.setCellValue(range.getTop(), range.getLeft(), value);
}
</zscript>
</window>
Download
Conclusion
ZK developers should have a general idea about new features of ZK Spreadsheet. From now on, ZK developers are able to access the data from ZK Spreadsheet easily. If you have any questions or suggestions, please feel free to leave comments here or post to ZK forum.
| Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License. |